<?php
class ASmsWapmaster extends CActiveRecord
{
    public function getAllserviceNumber()
    {
        $sql = "SELECT DISTINCT (serviceNumber) FROM c_sms_log ORDER BY serviceNumber";
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryAll();
        return $rows;
    }
    
    public function GetUser($search, $currentPage, $numberRecordPerPage)
    {
        $search["fromDate"] .= " 23:59:59";
        $startRecord = ($currentPage - 1) * $numberRecordPerPage;
        
        $condition = "";        
        $condition .= " AND create_date>='" . strtotime($search["toDate"]) . "'";
        $condition .= " AND create_date<='" . strtotime($search["fromDate"]) . "'";        
        $condition .= !empty($search["telco"]) ? " AND telco=" . mysql_escape_string($search["telco"]) : "";        
        $condition .= !empty($search["serviceNumber"]) ? " AND serviceNumber=" . mysql_escape_string($search["serviceNumber"]) : "";        
        $condition .= !empty($search["price"]) ? " AND price=" . mysql_escape_string($search["price"]) : "";        
        $condition .= " AND status=1";        
        
        if(!empty($search["keyword"])){
            $sql = "SELECT id FROM c_user WHERE username='" . mysql_escape_string($search["keyword"]) . "'";
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryRow();    
            
            $condition .= empty($rows) ? " AND userId=0" : " AND userId=" . $rows["id"];
        }
          
        /* Tiêu chí sắp xếp mặc định */      
        $sql = "SELECT userId, sum(price) as total_price FROM c_sms_log WHERE 1 " . $condition . " GROUP BY userId ORDER BY total_price DESC LIMIT " . $startRecord . ", " . $numberRecordPerPage;        
        
        if($search["order"]=="1") {
            /* Sắp xếp theo doanh thu từ thấp đến cao */
            $sql = "SELECT userId, sum(price) as total_price FROM c_sms_log WHERE 1 " . $condition . " GROUP BY userId ORDER BY total_price ASC LIMIT " . $startRecord . ", " . $numberRecordPerPage;        
        } else if($search["order"]=="2") {
            /* Sắp xếp theo doanh thu từ cao đến thấp */
            $sql = "SELECT userId, sum(price) as total_price FROM c_sms_log WHERE 1 " . $condition . " GROUP BY userId ORDER BY total_price DESC LIMIT " . $startRecord . ", " . $numberRecordPerPage;        
        } else if($search["order"]=="3") {
            /* Sắp xếp theo sản lượng từ thấp đến cao */
            $sql = "SELECT userId, count(id) as total_sms FROM c_sms_log WHERE 1 " . $condition . " GROUP BY userId ORDER BY total_sms ASC LIMIT " . $startRecord . ", " . $numberRecordPerPage;        
        } else if($search["order"]=="4") {
            /* Sắp xếp theo sản lượng từ cao đến thấp */
            $sql = "SELECT userId, count(id) as total_sms FROM c_sms_log WHERE 1 " . $condition . " GROUP BY userId ORDER BY total_sms DESC LIMIT " . $startRecord . ", " . $numberRecordPerPage;        
        }  
        
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryAll();    
        return $rows;
    }
    
    public function GetTotalUser($search)
    {
        $search["fromDate"] .= " 23:59:59";
        $condition = "";        
        $condition .= " AND create_date>='" . strtotime($search["toDate"]) . "'";
        $condition .= " AND create_date<='" . strtotime($search["fromDate"]) . "'";        
        $condition .= !empty($search["telco"]) ? " AND telco=" . mysql_escape_string($search["telco"]) : "";        
        $condition .= !empty($search["serviceNumber"]) ? " AND serviceNumber=" . mysql_escape_string($search["serviceNumber"]) : "";        
        $condition .= !empty($search["price"]) ? " AND price=" . mysql_escape_string($search["price"]) : "";        
        $condition .= " AND status=1";
        
        if(!empty($search["keyword"])){
            $sql = "SELECT id FROM c_user WHERE username='" . mysql_escape_string($search["keyword"]) . "'";
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryRow();    
            
            $condition .= empty($rows) ? " AND userId=0" : " AND userId=" . $rows["id"];
        }
                
        $sql = "SELECT count(DISTINCT userId) FROM c_sms_log WHERE 1 " . $condition;                        
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryRow();    
    
        return $rows["count(DISTINCT userId)"];    
    }
    
    public function getReport($search, $rate_sms)
    {
        $search["fromDate"] .= " 23:59:59";
        $sms = array();
        $condition = "";
        $condition .= $search["list_user_id"]!="" ? " AND userId IN (" . mysql_escape_string($search["list_user_id"]) . ")" : "";
        $condition .= " AND create_date>='" . strtotime($search["toDate"]) . "'";
        $condition .= " AND create_date<='" . strtotime($search["fromDate"]) . "'";        
        $condition .= !empty($search["telco"]) ? " AND telco=" . mysql_escape_string($search["telco"]) : "";        
        $condition .= !empty($search["serviceNumber"]) ? " AND serviceNumber=" . mysql_escape_string($search["serviceNumber"]) : "";        
        $condition .= !empty($search["price"]) ? " AND price=" . mysql_escape_string($search["price"]) : "";        
        
        $sql = "SELECT count(id), userId, price, month, year, type, serviceNumber,percent_wapmaster,percent_partner FROM c_sms_log WHERE status=1 " . $condition . " GROUP BY userId, month, year, price, type, serviceNumber,percent_wapmaster,percent_partner";        
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryAll();
                
        foreach($rows as $row){
            $id = $row["userId"];            
            $index_rate = $row["month"] . "_" . $row["year"];  
            $sc = preg_replace("/(\d+)(\d+)(\d+)(\d+)/si", "X$2XX", $row["serviceNumber"]);
            $type = $row["type"];
            
            if(!isset($sms[$id]["total_sms"])) $sms[$id]["total_sms"] = 0;
            if(!isset($sms[$id]["500"])) $sms[$id]["500"] = 0;
            if(!isset($sms[$id]["1000"])) $sms[$id]["1000"] = 0;
            if(!isset($sms[$id]["2000"])) $sms[$id]["2000"] = 0;
            if(!isset($sms[$id]["3000"])) $sms[$id]["3000"] = 0;
            if(!isset($sms[$id]["4000"])) $sms[$id]["4000"] = 0;
            if(!isset($sms[$id]["5000"])) $sms[$id]["5000"] = 0;
            if(!isset($sms[$id]["10000"])) $sms[$id]["10000"] = 0;
            if(!isset($sms[$id]["15000"])) $sms[$id]["15000"] = 0;
            if(!isset($sms[$id]["telco"])) $sms[$id]["telco"] = 0;
            if(!isset($sms[$id]["serviceNumber"])) $sms[$id]["serviceNumber"] = 0;
            if(!isset($sms[$id]["wapmaster"])) $sms[$id]["wapmaster"] = 0;
            if(!isset($sms[$id]["mtop"])) $sms[$id]["mtop"] = 0;
            if(!isset($sms[$id]["partner"])) $sms[$id]["partner"] = 0;
            
            $sms[$id]["total_sms"] += $row["count(id)"];
            $sms[$id]["500"] += $row["price"]==500 ? $row["count(id)"] : 0;
            $sms[$id]["1000"] += $row["price"]==1000 ? $row["count(id)"] : 0;
            $sms[$id]["2000"] += $row["price"]==2000 ? $row["count(id)"] : 0;
            $sms[$id]["3000"] += $row["price"]==3000 ? $row["count(id)"] : 0;
            $sms[$id]["4000"] += $row["price"]==4000 ? $row["count(id)"] : 0;
            $sms[$id]["5000"] += $row["price"]==5000 ? $row["count(id)"] : 0;
            $sms[$id]["10000"] += $row["price"]==10000 ? $row["count(id)"] : 0;
            $sms[$id]["15000"] += $row["price"]==15000 ? $row["count(id)"] : 0;
            
            $total_money = $row["price"] * $row["count(id)"];
            
            $rateSms = $rate_sms[$index_rate][$sc] / 100;
            $sms[$id]["telco"] += $total_money * $rateSms;  
            $sms[$id]["serviceNumber"] += $total_money * $rateSms * 0.85;        
        
            $sms[$id]["partner"] += $total_money * $rateSms  * ($row["percent_partner"] / 100);
            $sms[$id]["wapmaster"] += $total_money * $rateSms * 0.85 * ($row["percent_wapmaster"] / 100)*0.9;
            $sms[$id]["mtop"]+= $total_money * $rateSms *0.9 * (100-$row["percent_partner"]-$row["percent_wapmaster"])/100;
        } 
             
        $sql = "SELECT id, username FROM c_user WHERE id IN (" . $search["list_user_id"] . ")";
        $command = Yii::app()->db->createCommand($sql);
        $rowsUser = $command->queryAll();
        $replaceUser = array();
        foreach($rowsUser as $row){
            $replaceUser[$row["id"]]["id"] = $row["id"];
            $replaceUser[$row["id"]]["username"] = $row["username"];            
        }
                
        foreach($sms as $i=>$row){
            $sms[$i]["username"] = isset($replaceUser[$i]["username"]) ? $replaceUser[$i]["username"] : "";            
        }
                  
        $users = explode(", ", $search["list_user_id"]);
        $response = array();
        foreach($users as $id){
            if(isset($sms[$id])){
                $response[$id] = $sms[$id];
            }   
        }
        
        return $response;
    }
    
    public function getReportByUser($search, $rate_sms, $currentPage, $numberRecordPerPage)
    {
        $search["fromDate"] .= " 23:59:59";
        $startRecord = ($currentPage - 1) * $numberRecordPerPage;
        
        $condition = "";
        $condition .= " AND userId=" . $search["userId"];
        $condition .= " AND create_date>='" . strtotime($search["toDate"]) . "'";
        $condition .= " AND create_date<='" . strtotime($search["fromDate"]) . "'";        
        $condition .= " AND status=1";                
        
        $sql = "SELECT * FROM c_sms_log WHERE 1 " . $condition . " ORDER BY create_date DESC LIMIT " . $startRecord . ", " . $numberRecordPerPage;
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryAll();
        
        
        
        $i = 0;
        while(isset($rows[$i])){
            $type = $rows[$i]["type"];
            $dataId = $rows[$i]["dataId"];            
            $partnerId = $rows[$i]["partnerId"];   
            $sql = "SELECT name FROM c_partner WHERE id='" . $partnerId . "'";
            $command = Yii::app()->db->createCommand($sql);
            $rowPartner = $command->queryRow();             
            
            $rows[$i]["partner_name"] = !empty($rowPartner['name']) ? $rowPartner['name'] : "Không xác định";
            
            if($type=="1"){
                $rows[$i]["title"] = "Game Online" ;
            } else if($type=="2"){
                $rows[$i]["title"] = "Game Offline" ;
            } else if($type=="3"){
                $rows[$i]["title"] = "Clip" ;
            } else if($type=="4"){
                $rows[$i]["title"] = "App" ;    
            } else if($type=="5"){
                $rows[$i]["title"] = "Kích hoạt APK" ;
            } else if($type=="6"){
                $rows[$i]["title"] = "Kích hoạt Java";                    
            } else if($type=="7"){
                $rows[$i]["title"] = "Charging";                    
            }
            
            $rows[$i]["title"] = isset($rows[$i]["title"]) ? $rows[$i]["title"] : "Không xác định";
            
            $index_rate = $rows[$i]["month"] . "_" . $rows[$i]["year"];
            $sc = preg_replace("/(\d+)(\d+)(\d+)(\d+)/si", "X$2XX", $rows[$i]["serviceNumber"]);
            $rateSms = $rate_sms[$index_rate][$sc] / 100;
            $rows[$i]["telco"] = $rows[$i]["price"] * $rateSms;  
            
            $rows[$i]["wapmaster"] = $rows[$i]["telco"] *0.85* ($rows[$i]["percent_wapmaster"] / 100) *0.9;
            $rows[$i]["partner"] = $rows[$i]["telco"] * ($rows[$i]["percent_partner"] / 100);
            $rows[$i]["mtop"] = $rows[$i]["telco"] * 0.9 *((100 - $rows[$i]["percent_wapmaster"] - $rows[$i]["percent_partner"]) / 100);
            
            $i++;
        }

        return $rows;
    }
    
    public function getTotalReportByUser($search)
    {
        $search["fromDate"] .= " 23:59:59";
        $condition = "";
        $condition .= " AND userId=" . $search["userId"];
        $condition .= " AND create_date>='" . strtotime($search["toDate"]) . "'";
        $condition .= " AND create_date<='" . strtotime($search["fromDate"]) . "'";        
        $condition .= " AND status=1";                
        
        $sql = "SELECT count(id) FROM c_sms_log WHERE 1 " . $condition;
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryRow();        
        return $rows["count(id)"];
    }
}  
?>
